Solution 1: Simplify the Relationship

Let's learn how we can simplify the DB relationships to avoid Polymorphic Association antipattern.

We'll cover the following

Reverse the reference

It’s better to redesign our database to avoid the weaknesses of Polymorphic Associations but still be able to support the data modeling we need. We will be seeing a few solutions that accommodate the data relationship but make better use of metadata to enforce integrity. These are:

Reverse the reference#

One solution to this antipattern is simple once the nature of the problem becomes evident: Polymorphic Associations are backward.

Creating intersection tables#

A foreign key in the child table Comments can’t reference multiple parent tables, so, instead, we use multiple foreign keys to reference the Comments table. To do this, we create a separate intersection table for each parent table. In each intersection table, we include a foreign key to Comments and a foreign key to the respective parent table.

CREATE TABLE BugsComments (
issue_id        BIGINT UNSIGNED NOT NULL,
comment_id      BIGINT UNSIGNED NOT NULL,
PRIMARY KEY (issue_id, comment_id),
FOREIGN KEY (issue_id) REFERENCES Bugs(issue_id),
FOREIGN KEY (comment_id) REFERENCES Comments(comment_id)
);
CREATE TABLE FeaturesComments (
issue_id        BIGINT UNSIGNED NOT NULL,
comment_id      BIGINT UNSIGNED NOT NULL,
PRIMARY KEY (issue_id, comment_id),
FOREIGN KEY (issue_id) REFERENCES FeatureRequests(issue_id),
FOREIGN KEY (comment_id) REFERENCES Comments(comment_id)
);

Enter to Rename, Shift+Enter to Preview

Creating intersection tables for each parent table

The design is illustrated in the Entity-Relationship Diagram below.

This solution removes the need for the Comments.issue_type column. Now the metadata can enforce data integrity instead of relying on application code to manage the associations without error.

Putting up traffic lights#

A potential weakness of this solution is that it permits associations that we might not want. Intersection tables usually model many-to-many associations, so this would allow a given comment to be associated with multiple bugs or multiple feature requests. However, we normally want each comment to pertain to only one bug or one feature request. We can enforce at least part of this rule by declaring a UNIQUE constraint on each intersection table’s comment_id column.

Enter to Rename, Shift+Enter to Preview

Creating BugsComments table having the UNIQUE constraint

This ensures that a given comment can be referenced only once in the intersection table, which naturally prevents it from being associated with multiple bugs or multiple feature requests. However, the metadata doesn’t prevent a given comment from being referenced in both intersection tables and being associated with both a bug and a feature request. Thus, though it’s tedious, restricting the ability to reference comments remains the responsibility of our application code.

Looking both ways#

We can query comments, given a specific bug or feature request, simply by using the intersection table.

Enter to Rename, Shift+Enter to Preview

Retrieving records for issue_id 1234

We can query the matching bug or feature request based on an instance of a comment by using an OUTER JOIN in both intersection tables. We have to name all the possible parent tables, but that’s no more complex than the query we had to use in the Polymorphic Associations antipattern. Also, we can depend on referential integrity when using intersection tables, whereas, with Polymorphic Associations, we can’t.

Enter to Rename, Shift+Enter to Preview

Retrieving data for comment_id 9876

Merging lanes#

Sometimes we need to make the result of a query against multiple parent tables appear as if we had stored the parents in a single table (see Single Table Inheritance). We can do this in either of two ways — UNION and COALESCE().

First, let’s look at the following query using UNION:

SELECT b.issue_id, b.description, b.reporter, b.priority, b.status,
b.severity, b.version_affected,
NULL AS sponsor
FROM Comments AS c
JOIN (BugsComments JOIN Bugs AS b USING (issue_id))
USING (comment_id)
WHERE c.comment_id = 9876
UNION
SELECT f.issue_id, f.description, f.reporter, f.priority, f.status,
NULL AS severity, NULL AS version_affected,
f.sponsor
FROM Comments AS c
JOIN (FeaturesComments JOIN FeatureRequests AS f USING (issue_id))
USING (comment_id)
WHERE c.comment_id = 9876;

Enter to Rename, Shift+Enter to Preview

Combining results of two queries using UNION

This query is guaranteed to return a single row if our application has associated each comment with exactly one parent table. Since query results can be combined with UNION only if their columns are the same in number and data type, we must provide null placeholders for columns that are unique to each parent table. It would be best if we list the columns in the same order in both queries involved in the UNION.

Alternatively, let’s look at the following query using the SQL COALESCE() function. This function returns its first non-null argument. Since we are using an OUTER JOIN in the query, a comment that pertains to a feature request and has no matching row in Bugs would return all fields in b.\* as null. Likewise, all fields in f.\* would be null if the comment pertains to a bug instead of a feature request. We can list the fields specific to one parent table or the other; if they are irrelevant to the matching parent table, they are returned as null.

SELECT c.*,
COALESCE(b.issue_id, f.issue_id ) AS issue_id,
COALESCE(b.description, f.description) AS description,
COALESCE(b.reporter, f.reporter ) AS reporter,
COALESCE(b.priority, f.priority ) AS priority,
COALESCE(b.status, f.status ) AS status,
b.severity,
b.version_affected,
f.sponsor
FROM Comments AS c
LEFT OUTER JOIN (BugsComments JOIN Bugs AS b USING (issue_id))
USING (comment_id)
LEFT OUTER JOIN (FeaturesComments JOIN FeatureRequests AS f USING (issue_id))
USING (comment_id)
WHERE c.comment_id = 9876;

Enter to Rename, Shift+Enter to Preview

Combining results of two queries using COALESCE()

Both of these queries are pretty complex, so they’re good candidates for a database view. We can probably use them more simply in our application.

Antipattern: Use Dual-Purpose Foreign Key

Solution 2: Create a Common Super-Table

Mark as Completed

Report an Issue